CREATE TABLE [dbo].[CertificationProgram]
(
[CertificationProgramKey] [uniqueidentifier] NOT NULL,
[CertificationProgramName] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CertificationProgramDescription] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddDesignationFlag] [bit] NOT NULL,
[AutoEnrollFlag] [bit] NOT NULL,
[UomKey] [uniqueidentifier] NULL,
[MinimumValue] [decimal] (18, 0) NULL,
[MaximumValue] [decimal] (18, 0) NULL,
[CertificationProgramStatusCode] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RequiresApprovalFlag] [bit] NOT NULL CONSTRAINT [DF_CertificationProgram_RequiresApprovalFlag] DEFAULT ('0'),
[ProcessBlueprint] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequiredNumberOfUnits] [numeric] (18, 8) NULL,
[StagesInOrder] [bit] NOT NULL,
[DeadlinePeriodTimeUnit] [bigint] NOT NULL,
[DeadlinePeriodMultiplier] [int] NOT NULL,
[GracePeriodTimeUnit] [bigint] NULL,
[GracePeriodMultiplier] [int] NULL,
[CertificationProgramGroupKey] [uniqueidentifier] NULL,
[PreRequiredProgramKey] [uniqueidentifier] NULL,
[IsMultipleIterationFlag] [bit] NOT NULL CONSTRAINT [DF_CertificationProgram_IsMultipleIterationFlag] DEFAULT ('0'),
[FormatCertificationNumber] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedOn] [datetime] NOT NULL,
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedByUserKey] [uniqueidentifier] NULL,
[MarkedForDeleteOn] [datetime] NULL,
[LastCertificationNumber] [int] NULL,
[Designation] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[CertificationProgram] ADD CONSTRAINT [PK_CertificationProgram_CertificationProgramKey] PRIMARY KEY CLUSTERED ([CertificationProgramKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CertificationProgram_CertificationProgramGroupKey] ON [dbo].[CertificationProgram] ([CertificationProgramGroupKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CertificationProgram_CertificationProgramStatusCode] ON [dbo].[CertificationProgram] ([CertificationProgramStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CertificationProgram_CreatedByUserKey] ON [dbo].[CertificationProgram] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CertificationProgram_PreRequiredProgramKey] ON [dbo].[CertificationProgram] ([PreRequiredProgramKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CertificationProgram_UpdatedByUserKey] ON [dbo].[CertificationProgram] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CertificationProgram] ADD CONSTRAINT [FK_CertificationProgram_CertificationProgram] FOREIGN KEY ([PreRequiredProgramKey]) REFERENCES [dbo].[CertificationProgram] ([CertificationProgramKey])
GO
ALTER TABLE [dbo].[CertificationProgram] ADD CONSTRAINT [FK_CertificationProgram_CertificationProgramGroup] FOREIGN KEY ([CertificationProgramGroupKey]) REFERENCES [dbo].[ProgramGroup] ([ProgramGroupKey])
GO
ALTER TABLE [dbo].[CertificationProgram] ADD CONSTRAINT [FK_CertificationProgram_ProgramStatusRef] FOREIGN KEY ([CertificationProgramStatusCode]) REFERENCES [dbo].[ProgramStatusRef] ([ProgramStatusCode])
GO
ALTER TABLE [dbo].[CertificationProgram] ADD CONSTRAINT [FK_CertificationProgram_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[CertificationProgram] ADD CONSTRAINT [FK_CertificationProgram_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO